import numpy as np
from flask import Flask, request, jsonify
import pandas as pd
import mysql.connector
from mysql.connector import Error
import os

app = Flask(__name__)


# 数据库连接配置
def get_db_connection():
    return mysql.connector.connect(
        host='101.34.81.209',  # MySQL 主机地址
        user='root',  # 用户名
        password='w1234567890',  # 密码
        database='data_echarts'  # 数据库名称
    )


# 导入 Excel 数据到数据库
@app.route('/import_excel', methods=['POST'])
def import_excel():
    if 'file' not in request.files:
        return jsonify({"error": "No file part"}), 400

    file = request.files['file']
    if file.filename == '':
        return jsonify({"error": "No selected file"}), 400

    if file and file.filename.endswith('.xlsx'):
        try:
            # 使用 pandas 读取 Excel 文件
            df = pd.read_excel(file)

            # 确保 Excel 文件的列与数据库表一致
            required_columns = ['年份', '人员id', '受教育年限 受教育程度 0=文盲/半文盲 6=小学 9=初中 12=高中/中专/技校/职高 15=大专 16=大学本科 19=硕士 22=博士'] #差 较差 正常 良好 非常好
            if not all(col in df.columns for col in required_columns):
                return jsonify({"error": "Excel file missing required columns"}), 400

            # 打开数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            # 存储要插入的数据
            data_to_insert = []
            for _, row in df.iterrows():
                year = row['年份']
                type_ = ''
                count = row['受教育年限 受教育程度 0=文盲/半文盲 6=小学 9=初中 12=高中/中专/技校/职高 15=大专 16=大学本科 19=硕士 22=博士']
                if count == 0:
                    type_ = '文盲/半文盲'
                elif count == 6:
                    type_ = '小学'
                elif count == 9:
                    type_ = '初中'
                elif count == 12:
                    type_ = '高中/中专/技校/职高'
                elif count == 15:
                    type_ = '大专'
                elif count == 16:
                    type_ = '大学本科'
                elif count == 19:
                    type_ = '硕士'
                elif count == 22:
                    type_ = '博士'
                else:
                    type_ = '文盲/半文盲'
                if np.isnan(count):
                    count = 0
                data_to_insert.append((year, type_, count))

                # 当数据达到 1000 条时进行批量插入
                if len(data_to_insert) == 1000:
                    cursor.executemany("""
                        INSERT INTO population_education_level_proportion_table (year, education, count)
                        VALUES (%s, %s, %s)
                    """, data_to_insert)
                    conn.commit()
                    print('data', data_to_insert)
                    data_to_insert = []
            # 插入剩余数据
            if data_to_insert:
                cursor.executemany("""
                    INSERT INTO population_education_level_proportion_table (year, education, count)
                    VALUES (%s, %s, %s)
                """, data_to_insert)
                print('data', data_to_insert)
                conn.commit()

            cursor.close()
            conn.close()

            return jsonify({"message": "Excel file successfully imported to database"}), 200

        except Exception as e:
            return jsonify({"error": str(e)}), 500
    else:
        return jsonify({"error": "Invalid file format, please upload an.xlsx file"}), 400


if __name__ == '__main__':
    app.run(debug=True)